﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using s2b.ControleDesenvolvimentoProjetos.Dados.OBJ;
using System.Collections;

namespace s2b.ControleDesenvolvimentoProjetos.Util.Auxiliar
{
    public class Auxiliar
    {
        static String conexString;
        static SqlConnection conexao;
        static SqlCommand comando;
        /// <summary>
        /// Abre a conexão
        /// </summary>
        /// <param name="comandoSQL"></param>
        private static void Conexao()
        {
            try
            {
                conexString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["CS_DB_Controle_Projeto"].ConnectionString;
                conexao = new System.Data.SqlClient.SqlConnection(conexString);
                comando = new System.Data.SqlClient.SqlCommand();
                comando.Connection = conexao;
                comando.CommandType = System.Data.CommandType.Text;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

        }

      

        /// <summary>
        /// Executa os comandos passados por parametro
        /// </summary>
        /// <param name="comandoSQL"></param>
        /// 

        public static void FecharConexao()
        {
            try
            {
               
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexao.Close();
            }

        }

        public static void ExecutaComando(string comandoSQL)
        {
            try
            {
             
                Conexao();
                comando.CommandText = comandoSQL;
                conexao.Open();
                comando.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {
                conexao.Close();
            }




        }
        /// <summary>
        /// Retorna os registros da table em forma de GridView
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="tabela"></param>
        /// <param name="select"></param>

        public static void RetornaTabela(GridView grid, string tabela, string select)
        {
            try
            {
                Conexao();
                comando.CommandText = select;
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(comando);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds, tabela);
                grid.DataSource = ds;
                grid.DataMember = tabela;
                grid.DataBind();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {
                conexao.Close();
            }


        }

        //public static SqlDataReader RetornaClientes(string select)
        //{
        //   // Cliente cliente;
        //    Conexao();
        //    comando.CommandText = select;
        //    SqlDataReader executa = comando.ExecuteReader();
        //    //while (executa.Read())
        //    //{
        //    //    cliente = new Cliente();
        //    //    cliente.Id_Cliente = Convert.ToInt32(executa["ID_CLIENTE"]);
        //    //    cliente.Razao_social = executa["RAZAO_SOCIAL"].ToString();
        //    //    clientes.Add(cliente);
        //    //}
        //    return executa;
        //}
        /// <summary>
        /// Retorna os registros da tabela
        /// </summary>
        /// <param name="select"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public  static SqlDataReader RetornaRegistros(string select, string table)
        {
            try
            {
                Conexao();
               // comando.CommandText = String.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", table);
                conexao.Open();
                //SqlDataReader drColumns = comando.ExecuteReader();
                //List<string> columns = new List<string>();
              
                //while (drColumns.Read())
                //{
                //    columns.Add(drColumns.GetString(0));
                   
                //}
                //drColumns.Close();

                comando.CommandText = select;
                SqlDataReader executa = comando.ExecuteReader();
        
                return executa;

            }
            catch (Exception ex)
            {
                return null;

            }
       

        }
       
        /// <summary>
        /// Retorna o id do registro
        /// </summary>
        /// <param name="e"></param>
        /// <param name="grid"></param>
        /// <param name="nomeIdRegistro"></param>
        /// <returns></returns>
        public static int RetornaIdRegistro(GridViewCommandEventArgs e, GridView grid, string nomeIdRegistro)
        {
            try
            {
                int idRegistro = -1;

                int index = int.Parse((string)e.CommandArgument);
                idRegistro = Convert.ToInt32(grid.DataKeys[index][nomeIdRegistro]);

                return idRegistro;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }

        }

        /// <summary>
        /// Deleta um registro
        /// </summary>
        /// <param name="e"></param>
        /// <param name="grid"></param>
        /// <param name="nomeIdRegistro"></param>
        /// <param name="tabela"></param>
        public static void DeletarRegistro(GridViewCommandEventArgs e, GridView grid, string nomeIdRegistro, string tabela)
        {
            Conexao();
            Auxiliar.ExecutaComando(String.Format("DELETE FROM {0} WHERE {1} = {2}", tabela, nomeIdRegistro, RetornaIdRegistro(e, grid, nomeIdRegistro)));

        }
    }
}